from openpyxl import load_workbook
from Tools.read_config import ReadConfig
from Tools import get_path
from Tools.Get_Cookies import GetData


class DoExcel:

    # 改动程序
    @classmethod
    def get_data(cls, file_name):
        mode = eval(ReadConfig.get_config(get_path.case_config_path, 'MODE', 'mode'))
        wb = load_workbook(file_name)


        tel = getattr(GetData, 'NoRegTel')  # 用反射的方式拿到数据
        # tel = GetData.NoRegTel #赋值的方式拿到excel替换数据
        test_data = []

        for key in mode:
            sheet = wb[key]
            if mode[key] == 'all':
                for i in range(2, sheet.max_row + 1):
                    row_data = {}
                    row_data['case_id'] = sheet.cell(i, 1).value
                    row_data['url'] = sheet.cell(i, 2).value
                    # row_data['data'] = sheet.cell(i, 3).value
                    if sheet.cell(i, 3).value.find('${tel}') != -1:  # 查找值，如果有
                        row_data['data'] = sheet.cell(i, 3).value.replace('${tel}', str(tel))
                        # row_data['data'] = sheet.cell(i, 3).value.replace('${tel_1}', str(getattr(GetData,'NoRegTel')))
                        tel += 1
                    if sheet.cell(i, 3).value.find('${normal_email}') != -1:
                        row_data['data'] = sheet.cell(i, 3).value.replace('${normal_email}',
                                                                          str(getattr(GetData, 'normal_email')))
                    if sheet.cell(i, 3).value.find('${email_password}') != -1:
                        row_data['data'] = row_data['data'].replace('${email_password}',
                                                                    str(getattr(GetData, 'email_password')))
                    elif sheet.cell(i, 3).value.find('${courseid}') != -1:
                        row_data['data'] = sheet.cell(i, 3).value.replace('${courseid}',
                                                                          str(getattr(GetData, 'courseid')))
                    elif sheet.cell(i, 3).value.find('${fromclassid}') != -1:
                        row_data['data'] = sheet.cell(i, 3).value.replace('${fromclassid}',
                                                                          str(getattr(GetData, 'fromclassid')))
                    elif sheet.cell(i, 3).value.find('${classid}') != -1:
                        row_data['data'] = sheet.cell(i, 3).value.replace('${classid}',
                                                                          str(getattr(GetData, 'classid')))
                    else:
                        row_data['data'] = sheet.cell(i, 3).value
                    row_data['title'] = sheet.cell(i, 4).value
                    row_data['http_method'] = sheet.cell(i, 5).value
                    row_data['response'] = sheet.cell(i, 6).value
                    row_data['expected'] = sheet.cell(i, 7).value
                    row_data['result'] = sheet.cell(i, 8).value
                    row_data['sheet_name'] = key
                    test_data.append(row_data)
                    cls.update_test_data(tel, file_name, 'init')  # 更新手机号，针对excel文件数据
                    # 另一种遍历替换方式
                    # for item in test_data:
                    #     if item['data']['mobilephone'] == '${tel}':
                    #         #替换数据
            else:
                for case_id in mode[key]:
                    row_data = {}
                    row_data['case_id'] = sheet.cell(case_id + 1, 1).value
                    row_data['url'] = sheet.cell(case_id + 1, 2).value
                    if sheet.cell(case_id, 3).valud.find('${tel}') != -1:
                        row_data['data'] = sheet.cell(case_id + 1, 3).value.replace('${tel}',
                                                                                    str(getattr(GetData, 'tel')))
                        tel += 1
                    elif sheet.cell(case_id, 3).value.find('${normal_email}') != -1:
                        row_data['data'] = sheet.cell(case_id, 3).value.replace('${normal_email}',
                                                                                str(getattr(GetData, 'normal_email')))
                    elif sheet.cell(case_id, 3).value.find('${normal_email}') != -1:
                        row_data['data'] = sheet.cell(case_id, 3).value.replace(GetData,
                                                                                str(getattr(GetData, 'email_password')))
                        print(sheet.cell(i,3))
                    elif sheet.cell(case_id, 3).value.find('${courseid}') != -1:
                        row_data['data'] = sheet.cell(case_id, 3).value.replace(GetData,
                                                                                str(getattr(GetData, '${courseid}')))
                    elif sheet.cell(case_id, 3).value.find('${fromclassid}') != -1:
                        row_data['data'] = sheet.cell(case_id, 3).value.replace(GetData,
                                                                                str(getattr(GetData, '${fromclassid}')))
                    elif sheet.cell(case_id, 3).value.find('${classid}') != -1:
                        row_data['data'] = sheet.cell(case_id, 3).value.replace(GetData,
                                                                                str(getattr(GetData, '${classid}')))
                    else:
                        row_data['data'] = sheet.cell(case_id, 3).value
                    row_data['title'] = sheet.cell(case_id, 4).value
                    row_data['http_method'] = sheet.cell(case_id, 5).value
                    row_data['response'] = sheet.cell(case_id, 6).value
                    row_data['expected'] = sheet.cell(case_id, 7).value
                    row_data['result'] = sheet.cell(case_id, 8).value
                    row_data['sheet_name'] = key
                    test_data.append(row_data)
        return test_data

    @staticmethod
    def write_result_data(file_name, sheet_name, i, result, test_result):
        wb = load_workbook(file_name)
        sheet = wb[sheet_name]
        sheet.cell(i, 6).value = result
        sheet.cell(i, 8).value = test_result
        wb.save(file_name)

    @classmethod
    def update_test_data(cls, tel, file_name, sheet_name):
        wb = load_workbook(file_name)
        sheet = wb[sheet_name]
        # wb['init'].cell(2, 1).value = tel
        sheet.cell(2, 1).value = tel
        wb.save(file_name)

# if __name__ == '__main__':
#     s=DoExcel.get_data(get_path.test_case_path)
#     print(s)

    # @staticmethod
    # def write_result_data(file_name, sheet_name, i, value=None):
    #     wb = load_workbook(file_name)
    #     sheet = wb[sheet_name]
    #     sheet.cell(i, 9).value = value
    #     wb.save(file_name)

    # def write_data(self, file_name, sheet_name, i, value, module_value):
    #     wb = load_workbook(file_name)
    #     sheet = wb[sheet_name]
    #     row_data = {}
    #     row_data['module'] = sheet.cell(i, 2).value
    #     if row_data['module'] == module_value:
    #         sheet.cell(i, 9).value = value
    #         wb.save(file_name)
    #
    # def write_excel_data(self, file_name, sheet_name, module_value, row_index_offset, column_index, value):
    #     wb = load_workbook(file_name)
    #     sheet = wb[sheet_name]
    #
    #     # 遍历除标题外的所有行
    #     for i in range(2, sheet.max_row + 1):
    #         if sheet.cell(i, 2).value == module_value:  # 假设'module'列是第二列
    #             # 计算实际要写入的行号（‌如果row_index_offset不为0）‌
    #             actual_row = i + row_index_offset
    #             # 写入数据到指定单元格
    #             sheet.cell(actual_row, column_index).value = str(value)

    # 保存工作簿
    # wb.save(file_name)

    # def test_write(self, test_data, file_name, sheet_name, module_value, row_index_offset, column_index, value):
    #     wb = load_workbook(file_name)
    #     sheet = wb[sheet_name]
    #
    #     for item in test_data:
    #         print(f"正在测试的用例是：{item['title']}")
    #         login_res = HTTPRequest('https://openapiv5.ketangpai.com//UserApi/login',
    #                                 '{"email": "790874262@qq.com", "password": "beiaidigan123!", "remember": "0"}').test_requests(
    #             item['url'], item['data'], item['http_method'])
    #         # 遍历除标题外的所有行
    #         for i in range(2, sheet.max_row + 1):
    #             if sheet.cell(i, 2).value == module_value:  # 假设'module'列是第二列
    #                 # 计算实际要写入的行号（‌如果row_index_offset不为0）‌
    #                 actual_row = i + row_index_offset
    #                 # 写入数据到指定单元格
    #                 sheet.cell(actual_row, column_index).value = str(value)
    #
    #         # 保存工作簿
    #         wb.save(file_name)
    #     return login_res

# if __name__ == '__main__':
#     test = DoExcel().get_data(get_path.test_case_path)
#     print(test)

# def get_xt(self, file_name, sheet_name):
#     wb = load_workbook(file_name)
#     sheet_name = wb[sheet_name]
#     df = pd.DataFrame(sheet_name)
#
#     filtered_df = df[df['module'] == 'login']
#     print(filtered_df)
#
# def get_module(self, file_name, sheet_name, column_to_match='login'):
#     wb = load_workbook(file_name)
#
#     sheet = wb[sheet_name]
#     test_data = []
#     current_module = None
#     module_data = []
#
#     for i in range(2, sheet.max_row + 1):
#         row_data = {}
#         row_data['case_id'] = sheet.cell(i, 1).value
#         row_data['module'] = sheet.cell(i, 2).value
#         row_data['url'] = sheet.cell(i, 3).value
#         row_data['data'] = sheet.cell(i, 4).value
#         row_data['title'] = sheet.cell(i, 5).value
#         row_data['http_method'] = sheet.cell(i, 6).value
#         row_data['expected'] = sheet.cell(i, 7).value
#         row_data['PASS/FAIL'] = sheet.cell(i, 8).value
#
#         if row_data[column_to_match] == current_module:
#             module_data.append(row_data)
#         else:
#             if module_data:  # 如果之前已经收集了数据，‌先将其添加到test_data中
#                 test_data.append(module_data)
#             module_data = [row_data]  # 开始收集新的module的数据
#             current_module = row_data[column_to_match]
#
#         # 不要忘记添加最后一个module的数据
#     if module_data:
#         test_data.append(module_data)
#
#     return test_data
#
# def getmodule(self, file_name, sheet_name, module_value):
#     wb = load_workbook(file_name)
#     sheet = wb[sheet_name]
#     test_data = []
#
#     # 遍历除标题外的所有行
#     for i in range(2, sheet.max_row + 1):
#         row_data = {}
#         row_data['case_id'] = sheet.cell(i, 1).value
#         row_data['module'] = sheet.cell(i, 2).value
#         row_data['url'] = sheet.cell(i, 3).value
#         row_data['data'] = sheet.cell(i, 4).value
#         row_data['title'] = sheet.cell(i, 5).value
#         row_data['http_method'] = sheet.cell(i, 6).value
#         row_data['expected'] = sheet.cell(i, 7).value
#         row_data['PASS/FAIL'] = sheet.cell(i, 8).value
#
#         # 检查当前行的'module'列是否与指定的值匹配
#         if row_data['module'] == module_value:
#             test_data.append(row_data)
#
#     return test_data

#
# sheet_data = DoExcel().get_all_sheets_data("../case_data/case_data.xlsx")
# print(sheet_data)

# print(DoExcel().getmodule("../case_data/case_data.xlsx", 'register', 'register'))
